const bcrypt = require('bcryptjs')
const {Op} = require('sequelize');
const {
    sequelize
} = require('../../core/db')


const {
    Sequelize,
    Model
} = require('sequelize')


class Booking extends Model {
    //根据设备编号
    static async getBookingHisList(mid) {
        const booking = await Booking.findAll({
            attributes: ['start', 'end'],
            where: {
                mid: mid,
                status: {
                    [Op.ne]: 2 // 使用Op.ne来表示不等于
                }
            }
        });
        return booking;
    }

    // 在Booking模型中修改静态方法
    // static async getBookingDataWithPagination(status, page, pageSize) {
    //     const offset = (page - 1) * pageSize;
    //     let data = null;
    //     let count = 0;
    //     let whereClause = '';
    //     let params = [];
    //
    //     if (status) {
    //         whereClause = ' AND b.status = ?';
    //         params.push(status);
    //     }
    //
    //     // 使用参数化查询
    //     data = await Booking.sequelize.query(
    //         `SELECT b.id as bId, e.name as mname, e.code as mcode, b.start, b.end, u.nickname as uname, e.status as eStatus, b.status as bStatus, b.memo, u.email as email
    //     FROM booking b
    //     INNER JOIN equipment e ON b.mid = e.id
    //     INNER JOIN user u ON b.uid = u.id
    //     WHERE 1=1 ${whereClause}
    //     LIMIT ? OFFSET ?`,
    //         {
    //             type: Booking.sequelize.QueryTypes.SELECT,
    //             replacements: [...params, pageSize, offset]
    //         }
    //     );
    //
    //     // 获取总行数，同样使用参数化查询
    //     count = await Booking.sequelize.query(
    //         `SELECT COUNT(*)
    //     FROM booking b
    //     INNER JOIN equipment e ON b.mid = e.id
    //     INNER JOIN user u ON b.uid = u.id
    //     WHERE 1=1 ${whereClause}`,
    //         {
    //             type: Booking.sequelize.QueryTypes.SELECT,
    //             replacements: params
    //         }
    //     );
    //
    //     return {data, total: count[0]['COUNT(*)']};
    // }

    // 在Booking模型中添加一个静态方法
    static async getBookingDataWithPagination(status, page, pageSize) {
        const offset = (page - 1) * pageSize;
        let data = null
        let count = 0
        // 执行SQL查询
        if (status === '') {
            data = await Booking.sequelize.query(
                `SELECT b.mid as mId,b.id as bId,e.name as mname, e.code as mcode, b.start, b.end, u.nickname as uname, e.status as eStatus, b.status as bStatus, b.memo,u.email as email
        FROM booking b, equipment e, user u
        WHERE mid = e.id AND uid = u.id
        LIMIT ${pageSize} OFFSET ${offset}`,
                {type: Booking.sequelize.QueryTypes.SELECT}
            );

            // 获取总行数
            count = await Booking.sequelize.query(
                `SELECT COUNT(*)
        FROM booking b, equipment e, user u
        WHERE mid = e.id AND uid = u.id`,
                {type: Booking.sequelize.QueryTypes.SELECT}
            );
        } else {
            data = await Booking.sequelize.query(
                `SELECT b.id as bId,e.name as mname, e.code as mcode, b.start, b.end, u.nickname as uname, e.status as eStatus, b.status as bStatus, b.memo
        FROM booking b, equipment e, user u
        WHERE mid = e.id AND uid = u.id and b.status =  ${status}
        LIMIT ${pageSize} OFFSET ${offset}`,
                {type: Booking.sequelize.QueryTypes.SELECT}
            );

            // 获取总行数
            count = await Booking.sequelize.query(
                `SELECT COUNT(*)
        FROM booking b, equipment e, user u
        WHERE mid = e.id AND uid = u.id`,
                {type: Booking.sequelize.QueryTypes.SELECT}
            );
        }


        return {data, total: count[0]['COUNT(*)']};
    }


    // static async getBookingDataWithPagination(status, page, pageSize) {
    //     const offset = (page - 1) * pageSize;
    //     let data = null;
    //     let count = 0;
    //     let whereClause = '';
    //     let params = [];
    //
    //     // 如果status存在且不为空，则添加到查询条件中
    //     if (status) {
    //         whereClause = ' AND b.status = ?';
    //         params.push(status);
    //     }
    //
    //     // 使用参数化查询以避免SQL注入
    //     data = await Booking.sequelize.query(
    //         `SELECT b.id as bId, e.name as mname, e.code as mcode, b.start, b.end, u.nickname as uname, e.status as eStatus, b.status as bStatus, b.memo, u.email as email
    //     FROM booking b
    //     INNER JOIN equipment e ON b.mid = e.id
    //     INNER JOIN user u ON b.uid = u.id
    //     WHERE 1=1 ${whereClause}
    //     LIMIT ? OFFSET ?`,
    //         {
    //             replacements: [...params, pageSize, offset],
    //             type: Booking.sequelize.QueryTypes.SELECT
    //         }
    //     );
    //
    //     // 获取总行数
    //     count = await Booking.sequelize.query(
    //         `SELECT COUNT(*) AS total
    //     FROM booking b
    //     INNER JOIN equipment e ON b.mid = e.id
    //     INNER JOIN user u ON b.uid = u.id
    //     WHERE 1=1 ${whereClause}`,
    //         {
    //             replacements: params,
    //             type: Booking.sequelize.QueryTypes.SELECT
    //         }
    //     );
    //
    //     return {data, total: count[0].total};
    // }


    static async getUserBookingDataWithPagination(page, pageSize, uid) {
        const offset = (page - 1) * pageSize;

        // 使用参数化查询来避免SQL注入
        const data = await Booking.sequelize.query(
            `SELECT b.id as bId, e.name as mname, e.code as mcode, b.start, b.end, u.nickname as uname, e.status as eStatus, b.status as bStatus, b.memo
        FROM booking b
        JOIN equipment e ON b.mid = e.id
        JOIN user u ON b.uid = u.id
        WHERE b.uid = :uid
        LIMIT :pageSize OFFSET :offset`,
            {
                type: Booking.sequelize.QueryTypes.SELECT,
                replacements: {
                    uid: uid,
                    pageSize: pageSize,
                    offset: offset
                }
            }
        );

        // 获取总行数，使用参数化查询
        const count = await Booking.sequelize.query(
            `SELECT COUNT(*) as count
        FROM booking b
        JOIN equipment e ON b.mid = e.id
        JOIN user u ON b.uid = u.id
        WHERE b.uid = :uid`,
            {
                type: Booking.sequelize.QueryTypes.SELECT,
                replacements: {uid: uid}
            }
        );

        return {data, total: count[0].count};
    }


    static async deleteMoudle(id) {
        const res = await Booking.destroy({
            where: {
                id
            }
        })
        return res
    }


    // 更新预约状态
    static async updateStatus(id, status, memo) {
        await this.update({status, memo}, {where: {id}});
    }


    // 查询预约时间是否有交集
    static async getRangeBoolean(mid, stime, etime) {
        // 将传入的时间字符串转换为日期对象
        const startDate = new Date(stime);
        const endDate = new Date(etime);

        // 查询数据库中mid对应的所有预约
        const bookings = await Booking.findAll({
            where: {
                mid: mid
            },
            attributes: ['status','start', 'end']
        });

        // 检查每个预约是否与给定时间区间有交集
        for (const booking of bookings) {
            const bookingStart = new Date(booking.start);
            const bookingEnd = new Date(booking.end);
            const sta = booking.status

            // 检查预约时间区间是否与给定时间区间有交集
            if ((bookingStart <= startDate && bookingEnd >= endDate || bookingStart <= endDate && bookingEnd >= startDate) && sta != 2) {
                return true; // 发现交集，返回true
            }
        }

        return false; // 没有发现交集，返回false
    }


}


Booking.init({
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    uid: Sequelize.INTEGER, // 用户id
    mid: Sequelize.INTEGER, //设备id
    start: Sequelize.DATE, // 添加 created_at 字段的对应属性
    end: Sequelize.DATE, // 添加 created_at 字段的对应属性
    status: Sequelize.INTEGER, // 添加 created_at 字段的对应属性
    createdAt: Sequelize.DATE, // 添加 created_at 字段的对应属性
    updatedAt: Sequelize.DATE, // 添加 updated_at 字段的对应属性
    memo: Sequelize.STRING, // 添加 updated_at 字段的对应属性
}, {
    sequelize,
    tableName: 'booking'
})


module.exports = {
    Booking
}


